/**
 * Author: Dao Anh Vu
 * 
 * 
 * 
 * */

package com.veritech.dao;

import java.sql.SQLException;
import java.util.List;

import javax.sql.DataSource;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

import java.sql.Connection;
import java.sql.PreparedStatement;

import com.veritech.mapper.ServiceMapper;
import com.veritech.model.Customer;
import com.veritech.model.Service;

public class ServiceDAO {
	private DataSource dataSource;
	private JdbcTemplate jdbcTemplate;
	
	public void setDataSource(DataSource ds){
		this.dataSource = ds;
		jdbcTemplate = new JdbcTemplate(dataSource);
	}
	
	public void saveService(Service service){
		if(service.getId() <= 0){
			final String sqlInsert = "insert into service(name, description, createdDate, active) values('" + service.getName() + "','"+
					service.getDescription() +"','" + service.getCreatedDate() + "', " + (service.isActive()?1:0) + ")";
			KeyHolder keyHolder = new GeneratedKeyHolder();
			jdbcTemplate.update(
				new PreparedStatementCreator(){
					public PreparedStatement createPreparedStatement(Connection conn) throws SQLException{
						PreparedStatement pstmt = conn.prepareStatement(sqlInsert, new String[]{"id"});
						
						return pstmt;
					}
				}, keyHolder);
			service.setId(keyHolder.getKey().longValue());
			return;
		}
		
		String sqlUpdate = "update service set name='" + service.getName() + "', description='" + service.getDescription() + "', createdDate=" +
				service.getCreatedDate() + "', active=" + (service.isActive()?1:0) + " where id=" + service.getId();
		jdbcTemplate.execute(sqlUpdate);
		return;
	}
	
	public List<Service> searchService(String name){
		String sql = "select id, name, createdDate, description, active from service where name like '%" + name + "%'";
		List<Service> list = jdbcTemplate.query(sql, new ServiceMapper());
		return list;
	}
	
	public List<Service> getServiceByCustomer(long custId){
		String sql = "select s.id, s.name, s.description, s.active ";
		sql += "from service s inner join request r on s.id = r.serviceId where r.customerId=" + custId;
		return jdbcTemplate.query(sql, new ServiceMapper());
	}
	
	public List<Service> getServiceByCustomer(Customer cust){
		String sql = "select s.id, s.name, s.description, s.active ";
				sql += "from service s inner join request r on s.id = r.serviceId where r.customerId=" + cust.getId();
		return jdbcTemplate.query(sql, new ServiceMapper());
	}
	
	public List<Service> getAllService(){
		String sql = "select id, name, createdDate, description, active from service";
		List<Service> list = jdbcTemplate.query(sql, new ServiceMapper());
		return list;
	}
	
	public Service getServiceById(long id) {
		String sql = "select id, name, createdDate, description, active from service where id=" + id;
		return jdbcTemplate.queryForObject(sql, new ServiceMapper());
	}
}
